package com.zx.jhpt.service;

import com.zx.framework.core.exception.ServiceException;

import com.zx.framework.core.util.DatetimeUtils;
import com.zx.framework.core.util.StringUtil;
import com.zx.framework.db.IBaseDAO;
import com.zx.jhpt.po.Fxfkjz;
import com.zx.jhpt.po.Replymessage;
import com.zx.jhpt.util.ExcelUtil;
import com.zx.jhpt.vo.FxfkjzVo;
import com.zx.jhpt.vo.ReplymessageVo;
import com.zx.jhpt.vo.JhptConts;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

@Component("fxfkjzService")
@Transactional
public class FxfkjzService {

    private static final Logger LOGGER = Logger.getLogger(FxfkjzService.class);
    @Resource
    private IBaseDAO baseDAO;
    
    @Resource
	private String jhptUpload;

    //通过风险点id来查找风险点
    public Fxfkjz findById(String id){
        return baseDAO.findById(id,Fxfkjz.class);
    }
    
    public List<Fxfkjz> getAll(){
    	String hql = "from Fxfkjz f order by f.orders";
    	return baseDAO.queryHQL(hql);
    }
    
  //风险报表，查询台帐信息
   public List<Map> analysis(FxfkjzVo example){
    	List<String> params = new ArrayList<String>();
    	
    	StringBuffer where = new StringBuffer("");
    	StringBuffer hcsjs_sql = new StringBuffer("");
    	if (null != example) {
    		if (StringUtil.isNotEmpty(example.getHcsj())) {
    			example.setHcsj(example.getHcsj()+",");
				String[] hcsjs = example.getHcsj().split(",");
				if (hcsjs.length != 0) {
					hcsjs_sql.append(" and ( ");
    				for (int i = 0; i < hcsjs.length; i++) {
    					hcsjs_sql.append(" to_char(r.hcsj, 'yyyy-MM') = ? ");
    					params.add(hcsjs[i]);
    					if (hcsjs.length -1 != i) {
    						hcsjs_sql.append(" OR ");
						}
    				}
    				hcsjs_sql.append(" ) ");
				}
			}
    		if (StringUtil.isNotEmpty(example.getFxdj())) {
    			where.append(" and f.fxdj = ? ");
    			params.add(example.getFxdj());
			}
    		if (StringUtil.isNotEmpty(example.getId())) {
    			example.setId(example.getId()+",");
    			String[] ids = example.getId().split(",");
    			if (ids.length != 0) {
    				where.append(" and ( ");
    				for (int i = 0; i < ids.length; i++) {
    					where.append(" f.id = ? ");
    					params.add(ids[i]);
    					if (ids.length -1 != i) {
    						where.append(" OR ");
						}
    				}
    				where.append(" ) ");
				}
			}
		}
    	/*String sql = "select r.id replid,r.deptid, r.fxid fxid,r.class1 class1,r.class2 class2,r.bmjkr bmjkr,r.fxdbmfzr fxdbmfzr," 
    			+ " r.fxdmc fxdmc,r.fxjkkj fxjkkj,r.fxdj fxdj,r.cjjkpv cjjkpv, " 
    			+ " r.deptname deptname,to_char(r.hcsj,'yyyy-MM') hcsj,r.fxwt fxwt,r.gdwfzr gdwfzr,r.gdwzgld gdwzgld,r.fksj fksj,r.sftghzd sftghzd," +
    			"r.gdwfkjg gdwfkjg,r.yjzgwcsj yjzgwcsj,r.qtbz qtbz"
    			+ " from replymessage r where 1=1 "+hcsjs_sql+"  "
    			+ "and r.deptid is not null " + where 
    			+ "order by r.ORDERS,r.deptid";*/
    	/*select f.id id,f.fxdj,r.id replid,r.deptid,r.deptname deptname,r.hcsj hcsj,  
    	r.fxwt fxwt,r.gdwfzr gdwfzr,r.gdwzgld gdwzgld,r.fksj fksj,r.sftghzd sftghzd,
    	r.gdwfkjg gdwfkjg,r.yjzgwcsj yjzgwcsj,r.qtbz qtbz, r.fxid fxid,
    	r.class1 class1,r.class2 class2,r.bmjkr bmjkr,r.fxdbmfzr fxdbmfzr,
    	r.fxdmc fxdmc,r.fxcyjtms fxcyjtms,r.fxjkkj fxjkkj,r.fxdj fxdj,r.zdflyjsox zdflyjsox,
    	r.fxfsqfkcs fxfsqfkcs,r.fxfshfkcs fxfshfkcs,r.cjjkpv cjjkpv, r.jccs jccs,r.mqhcsd mqhcsd,
    	r.jlbd jlbd,r.orders from fxfkjz f 
    	left join 
    	(select * from replymessage r where 1=1 and hcsj=to_date('2017-05','yyyy-MM')) r 
    	on f.id = r.fxid 
    	where r.deptid is not null and f.fxdj like '%高风险%' and r.class1 is not null
    	order by f.ORDERS,r.deptid */
    	
    	String sql = "select f.id id,f.fxdj,r.id replid,r.deptid,r.deptname deptname,to_char(r.hcsj,'yyyy-MM') hcsj, " 
    					+" r.fxwt fxwt,r.gdwfzr gdwfzr,r.gdwzgld gdwzgld,r.fksj fksj,r.sftghzd sftghzd, " 
    					+" r.gdwfkjg gdwfkjg,r.yjzgwcsj yjzgwcsj,r.qtbz qtbz, r.fxid fxid, " 
    					+" r.class1 class1,r.class2 class2,r.bmjkr bmjkr,r.fxdbmfzr fxdbmfzr, " 
    					+" r.fxdmc fxdmc,r.fxcyjtms fxcyjtms,r.fxjkkj fxjkkj,r.fxdj rfxdj,r.zdflyjsox zdflyjsox, " 
    					+" r.fxfsqfkcs fxfsqfkcs,r.fxfshfkcs fxfshfkcs,r.cjjkpv cjjkpv, r.jccs jccs,r.mqhcsd mqhcsd, " 
    					+" r.jlbd jlbd,r.orders from fxfkjz f " 
    					+ "left join "
    					+ "(select * from replymessage r where 1=1 "+hcsjs_sql+") r "
    					+ "on f.id = r.fxid "
    					+ "where r.deptid is not null " + where
    					+ "order by f.ORDERS,r.deptid";
    	
    	System.out.println("-------"+sql);
    	return baseDAO.querySQL(sql, params.toArray());
    }
    
    /*//风险报表，查询台帐信息
    public List<Map> analysis(FxfkjzVo example){
    	List<String> params = new ArrayList<String>();
    	
    	StringBuffer where = new StringBuffer("");
    	StringBuffer hcsjs_sql = new StringBuffer("");
    	if (null != example) {
    		if (StringUtil.isNotEmpty(example.getHcsj())) {
    			example.setHcsj(example.getHcsj()+",");
				String[] hcsjs = example.getHcsj().split(",");
				if (hcsjs.length != 0) {
					hcsjs_sql.append(" and ( ");
    				for (int i = 0; i < hcsjs.length; i++) {
    					hcsjs_sql.append(" to_char(r.hcsj, 'yyyy-MM') = ? ");
    					params.add(hcsjs[i]);
    					if (hcsjs.length -1 != i) {
    						hcsjs_sql.append(" OR ");
						}
    				}
    				hcsjs_sql.append(" ) ");
				}
			}//执行到这，hcsjs_sql=and (  to_char(r.hcsj, 'yyyy-MM') = ?  )；params=[2017-05]
    		if (StringUtil.isNotEmpty(example.getFxdj())) {
    			where.append(" and f.fxdj = ? ");
    			params.add(example.getFxdj());
			}
    		if (StringUtil.isNotEmpty(example.getId())) {
    			example.setId(example.getId()+",");
    			String[] ids = example.getId().split(",");
    			if (ids.length != 0) {
    				where.append(" and ( ");
    				for (int i = 0; i < ids.length; i++) {
    					where.append(" f.id = ? ");
    					params.add(ids[i]);
    					if (ids.length -1 != i) {
    						where.append(" OR ");
						}
    				}
    				where.append(" ) ");
				}
			}
		}
    	String sql = "select f.*,r.id replid,r.deptid,r.deptname,to_char(r.hcsj,'yyyy-MM') hcsj,r.fxwt,r.gdwfzr,r.gdwzgld,r.fksj,r.sftghzd,r.gdwfkjg,r.yjzgwcsj,r.qtbz "
    			+ "from fxfkjz f "
    			+ "left join "
    			+ "(select * from replymessage r where 1=1 "+hcsjs_sql+") r "
    			+ "on f.id = r.fxid "
    			+ "where r.deptid is not null " + where
    			+ "order by f.ORDERS,r.deptid";
    	select f.*,r.id replid,r.deptid,r.deptname,to_char(r.hcsj,'yyyy-MM') hcsj,
    	r.fxwt,r.gdwfzr,r.gdwzgld,r.fksj,r.sftghzd,r.gdwfkjg,r.yjzgwcsj,r.qtbz 
    	from fxfkjz f left join (select * from replymessage r where 1=1  
    	and (  to_char(r.hcsj, 'yyyy-MM') = ?  ) ) r on f.id = r.fxid 
    	where r.deptid is not null order by f.ORDERS,r.deptid
    	return baseDAO.querySQL(sql, params.toArray());
    }*/
    
    public List<Replymessage> query(FxfkjzVo example){
    	String hql = "from Replymessage where 1=1";
    	try {
    		List<Object> params = new ArrayList<>();
    		if(example!=null){
    			if(StringUtil.isNotEmpty(example.getHcsj())){
    				hql += " and hcsj = ?";
    				params.add(DatetimeUtils.stringToDate(example.getHcsj(), "yyyy-MM"));
    			}
    			if(StringUtil.isNotEmpty(example.getId())){
    				hql += " and fxid = ?";
    				params.add(example.getId());
    			}
    		}
			return baseDAO.queryHQL(hql, params.toArray());
		} catch (ParseException e) {
			e.printStackTrace();
			throw new ServiceException("时间格式转换错误！");
		}
    }
    
    public void update(Fxfkjz fxfkjz){
    	baseDAO.update(fxfkjz);
    }
    
    public HSSFWorkbook analdownload(List<Map> datas){
    	HSSFWorkbook wookBook = null;
    	// 创建Excel的工作书册 Workbook,对应到一个excel文档  
    	wookBook = new HSSFWorkbook();
    	// 创建Excel的工作sheet,对应到一个excel文档的tab  
        HSSFSheet sheet = wookBook.createSheet("风险报表");
        try {
        	HSSFFont font = wookBook.createFont();
        	font.setFontHeightInPoints((short) 10); // 字体高度
//        	font.setFontName(" 黑体 "); // 字体
            
            CellStyle cellStyle= wookBook.createCellStyle();
            cellStyle.setFont(font);
            //边框
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            //垂直居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
            //自动换行
            cellStyle.setWrapText(true);
            
//            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//            cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
          
            //设置行高
            sheet.createRow(0).setHeight((short)500);
            ExcelUtil.setCellValue(sheet, 0, 0, "风险点序", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 1, "一类", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 2, "二类", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 3, "部门接口", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 4, "风险点部", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 5, "风险点名", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 6, "风险监控", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 7, "风险风险等级", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 8, "筹建监控", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 9, "单位名称", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 10, "核查时间", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 11, "发现问题", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 12, "各单位负责人", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 13, "各单位主管领导", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 14, "反馈时间", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 15, "是否提供回执", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 16, "核查结果", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 17, "预计整改完成时间", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 18, "整改措施", 4, cellStyle);
//            ExcelUtil.setCellValue(sheet, 0, 19, "ID", 4, cellStyle);
            String temp = "";//记录风险ID
            int row = 0,temp_row = 0; 
            for (int i = 0; i < datas.size(); i++) {
            	Map map = datas.get(i);
            	//设置行高
            	sheet.createRow(i+1).setHeight((short)500);
            	ExcelUtil.setCellValue(sheet, i+1, 0, map.get("ID"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 1, map.get("CLASS1"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 2, map.get("CLASS2"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 3, map.get("BMJKR"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 4, map.get("FXDBMFZR"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 5, map.get("FXDMC"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 6, map.get("FXJKKJ"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 7, map.get("FXDJ"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 8, map.get("CJJKPV"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 9, map.get("DEPTNAME"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 10, map.get("HCSJ"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 11, map.get("FXWT"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 12, map.get("GDWFZR"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 13, map.get("GDWZGLD"), 4, cellStyle);
                String fksj = "";
                if(map.get("FKSJ") != null){
                	fksj = map.get("FKSJ").toString().substring(0, 16);
                }
                ExcelUtil.setCellValue(sheet, i+1, 14, fksj, 4, cellStyle);
                String sftghzd = "";
                if(map.get("SFTGHZD") != null){
                	if (map.get("SFTGHZD").toString().equals(JhptConts.REPLYMESSAGE_SFTGHZD_TRUE)) {
                		sftghzd = "是";
					}
                	if (map.get("SFTGHZD").toString().equals(JhptConts.REPLYMESSAGE_SFTGHZD_FALSE)) {
                		sftghzd = "否";
                	}
                }
                ExcelUtil.setCellValue(sheet, i+1, 15, sftghzd, 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 16, map.get("GDWFKJG"), 4, cellStyle);
                String yjzgwcsj = "";
                if(map.get("YJZGWCSJ") != null){
                	yjzgwcsj = map.get("YJZGWCSJ").toString().substring(0, 10);
                }
                ExcelUtil.setCellValue(sheet, i+1, 17, yjzgwcsj, 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 18, map.get("QTBZ"), 4, cellStyle);
//                ExcelUtil.setCellValue(sheet, i+1, 19, map.get("ID"), 4, cellStyle);
                if(StringUtil.isEmpty(temp)){
                	temp = map.get("ID").toString();
                	row = i;
                }
                boolean flag = false;
            	if (datas.size()-1 >= i+1 && !temp.equals(datas.get(i+1).get("ID").toString())) {
            		flag = true;
				}
            	if (i == datas.size() - 1) {
            		flag = true;
				}
            	if (flag) {
            		for (int j = 0; j < 9; j++) {//合并列数
            			ExcelUtil.mergeCell(sheet, row+1, i+1, j, j);
					}
            		temp = null;
				}
    		}
            //发现问题
            sheet.setColumnWidth(11, 5000);
            //反馈时间
            sheet.setColumnWidth(14, 4000);
            //各单位反馈结果
            sheet.setColumnWidth(16, 5000);
            //预计整改完成时间
            sheet.setColumnWidth(17, 5000);
            //其他备注
            sheet.setColumnWidth(18, 7000);
            
            
		} catch (Exception e) {
			LOGGER.error("生成风险报表失败：", e);
			return wookBook;
		}
        return wookBook;
    }

}
